package jsyf.oa.dao.yuce;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import jsyf.oa.entity.yuce.YuceSubEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;
import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import java.util.List;


/**
 * Created by y on 2018/3/19.
 */
@Repository
public class YuceSubDao extends BaseDao {

    public YuceSubDao() {
        super.iBaseEntity = new YuceSubEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, YuceSubEntity.class);
    }

    public List<JSONObject> getFirstByUserIdAndCompanyId(String userId, String companyId, String regionId, String firstMonth, String secondMonth, String thirdMonth,String type) {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT v_dvc_model.modelId,v_dvc_model.modelName\n" +
                ",v_dvc_model.companyName,IFNULL(v_dvc_num1.num ,0)as firstMonth\n" +
                ",IFNULL(v_dvc_num2.num ,0)as secondMonth\n" +
                ",IFNULL(v_dvc_num3.num ,0)as thirdMonth,2 as sortNo\n" +
                "FROM \n" +
                "(SELECT t_dvc_model.ID as modelId,t_dvc_model.MODEL_NAME,t_sys_company.`NAME` AS companyName,t_dvc_model.MODEL_NAME as modelName\n" +
                "FROM t_sys_company LEFT JOIN t_sys_company_2_brand ON t_sys_company_2_brand.COMPANY_ID=t_sys_company.ID\n" +
                "LEFT JOIN t_dvc_brand ON t_sys_company_2_brand.BRAND_ID=t_dvc_brand.ID\n" +
                "LEFT JOIN t_dvc_model ON t_dvc_brand.ID=t_dvc_model.BRAND_ID\n" +
                "WHERE t_sys_company.DEL_FLAG=0\n" +
                "AND t_dvc_brand.DEL_FLAG=0\n" +
                "AND t_dvc_model.DEL_FLAG=0\n" +
                "AND t_sys_company.PARENT_ID='201712161613432EAF27DBCCB7D0E6CA'\n" +
                "AND t_sys_company.Id='" + companyId + "'\n" +
                "ORDER BY t_dvc_model.SORT_NO ) as v_dvc_model\n" +
                "LEFT  JOIN(\n" +
                "SELECT t_wrt_customer.INTENTION_MODEL as modelName,COUNT(t_wrt_customer.INTENTION_MODEL) as num\n" +
                "FROM t_wrt_customer\n" +
                "WHERE 1=1 \n" +
                " and t_wrt_customer.BUSINESS_DATE like '%" + firstMonth + "%'\n" +
                "AND t_wrt_customer.INTENTION_BRAND='约翰迪尔'\n" +
                "AND t_wrt_customer.INTENTION_MODEL is not NULL\n" +
                "AND t_wrt_customer.CREATE_USER_ID in (SELECT v_sys_part_time_for_report.userId FROM v_sys_part_time_for_report \n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=v_sys_part_time_for_report.roleNameId\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=v_sys_part_time_for_report.userId\n" +
                "WHERE 1=1\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n" +
                "AND t_sys_role_name.`NAME` ='区域经理'\n" +
                "AND t_sys_user_2_region.REGION_ID in\n" +
                "(SELECT t_sys_user_2_region.REGION_ID as regionId\n" +
                "FROM t_sys_user_2_region LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID=t_sys_region.ID\n" +
                "WHERE 1=1 AND t_sys_region.DEL_FLAG=0 AND t_sys_region.PARENT_ID=0 AND t_sys_user_2_region.USER_ID='" + userId + "'\n");
        if (!StringUtils.isBlank(regionId)) {
            sb.append("AND t_sys_region.ID='" + regionId + "'\n");
        }
        sb.append("AND t_sys_region.COMPANY_ID='" + companyId + "'))\n" +
                "GROUP BY (t_wrt_customer.INTENTION_MODEL)\n" +
                ") as v_dvc_num1 ON v_dvc_num1.modelName=v_dvc_model.modelName\n" +
                "LEFT  JOIN(\n" +
                "SELECT t_wrt_customer.INTENTION_MODEL as modelName,COUNT(t_wrt_customer.INTENTION_MODEL) as num\n" +
                "FROM t_wrt_customer\n" +
                "WHERE 1=1 \n" +
                " and t_wrt_customer.BUSINESS_DATE like '%" + secondMonth + "%'\n" +
                "AND t_wrt_customer.INTENTION_BRAND='约翰迪尔'\n" +
                "AND t_wrt_customer.INTENTION_MODEL is not NULL\n" +
                "AND t_wrt_customer.CREATE_USER_ID in (SELECT v_sys_part_time_for_report.userId FROM v_sys_part_time_for_report \n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=v_sys_part_time_for_report.roleNameId\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=v_sys_part_time_for_report.userId\n" +
                "WHERE 1=1\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n" +
                "AND t_sys_role_name.`NAME` ='区域经理'\n" +
                "AND t_sys_user_2_region.REGION_ID in\n" +
                "(SELECT t_sys_user_2_region.REGION_ID as regionId\n" +
                "FROM t_sys_user_2_region LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID=t_sys_region.ID\n" +
                "WHERE 1=1 AND t_sys_region.DEL_FLAG=0 AND t_sys_region.PARENT_ID=0 AND t_sys_user_2_region.USER_ID='" + userId + "'\n");
        if (!StringUtils.isBlank(regionId)) {
            sb.append("AND t_sys_region.ID='" + regionId + "'\n");
        }

        sb.append("AND t_sys_region.COMPANY_ID='" + companyId + "'))\n" +
                "GROUP BY (t_wrt_customer.INTENTION_MODEL)\n" +
                ") as v_dvc_num2 ON v_dvc_num2.modelName=v_dvc_model.modelName\n" +
                "LEFT  JOIN(\n" +
                "SELECT t_wrt_customer.INTENTION_MODEL as modelName,COUNT(t_wrt_customer.INTENTION_MODEL) as num\n" +
                "FROM t_wrt_customer\n" +
                "WHERE 1=1 \n" +
                " and t_wrt_customer.BUSINESS_DATE like '%" + thirdMonth + "%'\n" +
                "AND t_wrt_customer.INTENTION_BRAND='约翰迪尔'\n" +
                "AND t_wrt_customer.INTENTION_MODEL is not NULL\n" +
                "AND t_wrt_customer.CREATE_USER_ID in (SELECT v_sys_part_time_for_report.userId FROM v_sys_part_time_for_report \n" +
                "LEFT JOIN t_sys_role_name ON t_sys_role_name.ID=v_sys_part_time_for_report.roleNameId\n" +
                "LEFT JOIN t_sys_user_2_region ON t_sys_user_2_region.USER_ID=v_sys_part_time_for_report.userId\n" +
                "WHERE 1=1\n" +
                "AND t_sys_role_name.DEL_FLAG=0\n" +
                "AND t_sys_role_name.`NAME` ='区域经理'\n" +
                "AND t_sys_user_2_region.REGION_ID in\n" +
                "(SELECT t_sys_user_2_region.REGION_ID as regionId\n" +
                "FROM t_sys_user_2_region LEFT JOIN t_sys_region ON t_sys_user_2_region.REGION_ID=t_sys_region.ID\n" +
                "WHERE 1=1 AND t_sys_region.DEL_FLAG=0 AND t_sys_region.PARENT_ID=0 AND t_sys_user_2_region.USER_ID='" + userId + "'\n");
        if (!StringUtils.isBlank(regionId)) {
            sb.append("AND t_sys_region.ID='" + regionId + "'\n");
        }
        sb.append("AND t_sys_region.COMPANY_ID='" + companyId + "'))\n" +
                "GROUP BY (t_wrt_customer.INTENTION_MODEL)\n" +
                ") as v_dvc_num3 ON v_dvc_num3.modelName=v_dvc_model.modelName\n");
        if(!StringUtils.isBlank(type)){
             sb.append(" UNION ALL SELECT \"\", \"\", t_sys_company.`NAME` as companyName, \"\",\"\",\"\",0 as sortNo");
             sb.append(" \n" +
                     "from t_sys_company WHERE t_sys_company.id='"+companyId+"'");
             sb.append(" UNION ALL SELECT \"\", \"机型\",\"\" , MONTH(CURDATE()) as firstMonth,MONTH(DATE_ADD(CURDATE(), interval 1 month)) as secondMonth,MONTH(DATE_ADD(CURDATE(), interval 2 month)) as thirdMonth,1 as sortNo\n" +
                     "\n");
             sb.append(" ORDER BY sortNo");
        }
        return super.queryForJsonList(sb.toString());
    }

    public List<JSONObject> getSubListByMainId(String mainId, String firstMonth, String secondMonth, String thirdMonth, Boolean sum) {
        StringBuffer sb = new StringBuffer();
        if (sum) {
            sb.append("select sum(v_rpt_sub_sum.firstMonth) as firstMonth\n" +
                    " ,sum(v_rpt_sub_sum.secondMonth) as secondMonth\n" +
                    " ,sum(v_rpt_sub_sum.thirdMonth) as thirdMonth\n" +
                    "from (\n");
        }
        sb.append("SELECT v_rpt_sub1.modelName,IFNULL(v_rpt_sub2.firstMonth,0) AS firstMonth" +
                ",IFNULL(v_rpt_sub3.secondMonth,0) AS secondMonth,IFNULL(v_rpt_sub4.thirdMonth,0) AS thirdMonth\n" +
                "FROM\n" +
                "(SELECT t_rpt_friday_yuce_sub.MODEL_NAME as modelName\n" +
                "FROM t_rpt_friday_yuce_sub\n" +
                "WHERE 1=1\n" +
                "AND t_rpt_friday_yuce_sub.MAIN_ID in ('" + mainId + "')\n" +
                "GROUP BY t_rpt_friday_yuce_sub.MODEL_NAME) as v_rpt_sub1\n" +
                "LEFT JOIN (SELECT t_rpt_friday_yuce_sub.MODEL_NAME as modelName,SUM(t_rpt_friday_yuce_sub.NUMBER) as firstMonth\n" +
                "FROM t_rpt_friday_yuce_sub\n" +
                "WHERE 1=1\n" +
                "AND t_rpt_friday_yuce_sub.MAIN_ID in ('" + mainId + "')\n" +
                "AND t_rpt_friday_yuce_sub.`MONTH` LIKE '%" + firstMonth + "'\n" +
                "GROUP BY t_rpt_friday_yuce_sub.MODEL_NAME) as v_rpt_sub2 ON v_rpt_sub1.modelName=v_rpt_sub2.modelName\n" +
                "LEFT JOIN (SELECT t_rpt_friday_yuce_sub.MODEL_NAME as modelName,SUM(t_rpt_friday_yuce_sub.NUMBER) as secondMonth\n" +
                "FROM t_rpt_friday_yuce_sub\n" +
                "WHERE 1=1\n" +
                "AND t_rpt_friday_yuce_sub.MAIN_ID in ('" + mainId + "')\n" +
                "AND t_rpt_friday_yuce_sub.`MONTH` LIKE '%" + secondMonth + "%'\n" +
                "GROUP BY t_rpt_friday_yuce_sub.MODEL_NAME) as v_rpt_sub3 ON v_rpt_sub1.modelName=v_rpt_sub3.modelName\n" +
                "LEFT JOIN (SELECT t_rpt_friday_yuce_sub.MODEL_NAME as modelName,SUM(t_rpt_friday_yuce_sub.NUMBER) as thirdMonth\n" +
                "FROM t_rpt_friday_yuce_sub\n" +
                "WHERE 1=1\n" +
                "AND t_rpt_friday_yuce_sub.MAIN_ID in ('" + mainId + "')\n" +
                "AND t_rpt_friday_yuce_sub.`MONTH` LIKE '%" + thirdMonth + "%'\n" +
                "GROUP BY t_rpt_friday_yuce_sub.MODEL_NAME) as v_rpt_sub4 ON v_rpt_sub1.modelName=v_rpt_sub4.modelName\n");
        if (sum) {
            sb.append(") as v_rpt_sub_sum\n");
        }
        return super.queryForJsonList(sb.toString());
    }

}
